ALTER PROCEDURE sprocCXCRMGetCarDetailSelectListBySetting
AS

Declare @OrderByOption smallint
--SET @OrderByOption = 1 -- c.CreatedDate ASC, cp.CoverageCommenceDate ASC
--SET @OrderByOption = 2 -- c.CreatedDate ASC, cp.CoverageCommenceDate DESC
--SET @OrderByOption = 3 -- c.CreatedDate DESC, cp.CoverageCommenceDate ASC
SET @OrderByOption = 4 -- c.CreatedDate DESC, cp.CoverageCommenceDate DESC
--SET @OrderByOption = 5 -- cp.CoverageCommenceDate ASC, c.CreatedDate ASC
--SET @OrderByOption = 6 -- cp.CoverageCommenceDate ASC, c.CreatedDate DESC
--SET @OrderByOption = 7 -- cp.CoverageCommenceDate DESC, c.CreatedDate ASC
--SET @OrderByOption = 8 -- cp.CoverageCommenceDate DESC, c.CreatedDate DESC
Declare @MinPremium float
SET @MinPremium = 10000

Declare @QuoteDateStart smallint
SET @QuoteDateStart = 30

Declare @QuoteDateEnd smallint
SET @QuoteDateEnd = 1

Declare @CommenceDateStart smallint
SET @CommenceDateStart = 0

Declare @CommenceDateEnd smallint
SET @CommenceDateEnd = 14

Declare @StartCommDate date
Declare @EndCommDate date

Declare @StartDate datetime
DEclare @EndDate datetime

Declare @strStartDate varchar(8)
DEclare @strEndDate varchar(8)

Select top 1	@MinPremium = MinPremium, 
				@QuoteDateStart = QuoteStart, 
				@QuoteDateEnd = QuoteEnd, 
				@CommenceDateStart = CommDateStart, 
				@CommenceDateEnd = CommDateEnd,
				@OrderByOption = OrderByOption
		From tblCXCRMLeadSetting Where [Status] = 1

--IF @MinPremium IS NULL SET @MinPremium = 10000
--IF @QuoteStart IS NULL SET @QuoteStart = 30
--IF @QuoteEnd IS NULL  SET @QuoteEnd = 1
--IF @CommenceDateStart IS NULL SET @CommenceDateStart = 0
--IF @CommenceDateEnd IS NULL SET @CommenceDateEnd = 14

Select @EndCommDate = GETDATE() + @CommenceDateEnd
Select @StartCommDate = GETDATE() + @CommenceDateStart
Select @StartDate = GETDATE() - @QuoteDateStart
Select @EndDate = GETDATE() - @QuoteDateEnd

Select @strStartDate = CONVERT(varchar(8), @StartDate, 112)
Select @strEndDate = CONVERT(varchar(8), @EndDate, 112)

Select @MinPremium MinPremium, @StartDate QuoteStartDate, @EndDate QuoteEndDate, @StartCommDate StartCommDate, @EndCommDate EndCommDate, @OrderByOption OrderByOption

Select top 50 c.RefCEUserId, c.CarId, cp.CoverageCommenceDate, c.CreatedDate from tblCarDetailTH c 
	INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
	Where c.RefCEUserId NOT IN
	(Select l.CustomerID from tblCXCRMAgentLeads l Where (Convert(varchar(8), l.CreatedDate, 112) Between @strStartDate And Convert(varchar(8), GETDATE(), 112)))
	AND (CONVERT(varchar(8), c.CreatedDate, 112) Between @strStartDate And @strEndDate) And c.RefCEUserId > 0
	AND (cp.CoverageCommenceDate Between @StartCommDate And @EndCommDate)
	AND c.RefCEUserId IN (Select T.RefCEUserID from tblCarInsuranceTempResultTH AS T
							Where (Convert(varchar(8), T.ModifiedDate, 112) Between @strStartDate And Convert(varchar(8), GETDATE(), 112))
							Group By RefCEUserId
							Having MIN(T.AnnualPremium) >= @MinPremium)
	ORDER BY
		-- 1 AND 5
		CASE	WHEN @OrderByOption = 1 THEN c.CreatedDate
				WHEN @OrderByOption = 5 THEN cp.CoverageCommenceDate
				END ASC,
		CASE	WHEN @OrderByOption = 1 THEN cp.CoverageCommenceDate
				WHEN @OrderByOption = 5 THEN c.CreatedDate
				END ASC,
		-- 2 AND 6
		CASE	WHEN @OrderByOption = 2 THEN c.CreatedDate
				WHEN @OrderByOption = 6 THEN cp.CoverageCommenceDate
				END ASC,
		CASE	WHEN @OrderByOption = 2 THEN cp.CoverageCommenceDate
				WHEN @OrderByOption = 6 THEN c.CreatedDate
				END DESC,
		-- 3 AND 7
		CASE	WHEN @OrderByOption = 3 THEN c.CreatedDate
				WHEN @OrderByOption = 7 THEN cp.CoverageCommenceDate
				END DESC,
		CASE	WHEN @OrderByOption = 3 THEN cp.CoverageCommenceDate
				WHEN @OrderByOption = 7 THEN c.CreatedDate
				END ASC,
		-- 4 AND 8
		CASE	WHEN @OrderByOption = 4 THEN c.CreatedDate
				WHEN @OrderByOption = 8 THEN cp.CoverageCommenceDate
				END DESC,
		CASE	WHEN @OrderByOption = 4 THEN cp.CoverageCommenceDate
				WHEN @OrderByOption = 8 THEN c.CreatedDate
				END DESC

Select COUNT(c.RefCEUserID) CustomerCount, MIN(c.CreatedDate) MinQuoteDate, MAX(c.CreatedDate) MaxQuoteDate, MIN(cp.CoverageCommenceDate) MinCoverageCommenceDate, MAX(cp.CoverageCommenceDate) MaxCoverageCommenceDate  from tblCarDetailTH c 
	INNER JOIN tblCarPolicy cp ON c.CarID = cp.CECarID And c.RefCEUserID = cp.RefCEUserID
	Where c.RefCEUserId NOT IN
	(Select l.CustomerID from tblCXCRMAgentLeads l Where (Convert(varchar(8), l.CreatedDate, 112) Between @strStartDate And Convert(varchar(8), GETDATE(), 112)))
	AND (CONVERT(varchar(8), c.CreatedDate, 112) Between @strStartDate And @strEndDate) And c.RefCEUserId > 0
	AND (cp.CoverageCommenceDate Between @StartCommDate And @EndCommDate)
	AND c.RefCEUserId IN (Select T.RefCEUserID from tblCarInsuranceTempResultTH AS T
							Where (Convert(varchar(8), T.ModifiedDate, 112) Between @strStartDate And Convert(varchar(8), GETDATE(), 112))
							Group By RefCEUserId
							Having MIN(T.AnnualPremium) >= @MinPremium)